package com.china08.yxyapi.service.sws.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.service.sws.EdbWebGroupService;

@Service
public class EdbWebGroupServiceImpl implements EdbWebGroupService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public Map<String, Object> HomeSchoolGroupList(String provinceId, String cityId, String districtId, String phase, String paixu, String keyword, Integer pageNum) {

        List<Object> paramList = new ArrayList<Object>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                              ");
        sb1.append("   count(school_id)                                  ");
        sb1.append(" from yxy_sws.sws_group                              ");
        sb1.append(" where 1 = 1                                         ");
        sb1.append(" and is_test = false                                 ");
        if (StringUtils.isNotBlank(provinceId)) {
            sb1.append(" and area_id_province = ?                        ");
            paramList.add(provinceId);
        }
        if (StringUtils.isNotBlank(cityId)) {
            sb1.append(" and area_id_city = ?                            ");
            paramList.add(cityId);
        }
        if (StringUtils.isNotBlank(districtId)) {
            sb1.append(" and area_id_district = ?                        ");
            paramList.add(districtId);
        }
        if (StringUtils.isNotBlank(phase) && "p0".equals(phase)) {
            sb1.append(" and is_st0 = true                               ");
        }
        if (StringUtils.isNotBlank(phase) && "p1".equals(phase)) {
            sb1.append(" and is_st1 = true                               ");
        }
        if (StringUtils.isNotBlank(phase) && "p2".equals(phase)) {
            sb1.append(" and is_st2 = true                               ");
        }
        if (StringUtils.isNotBlank(phase) && "p3".equals(phase)) {
            sb1.append(" and is_st3 = true                               ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and school_name like ?                          ");
            paramList.add(keyword);
        }

        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append(" select *                                            ");
        sb2.append(" from yxy_sws.sws_group                              ");
        sb2.append(" where 1 = 1                                         ");
        sb2.append(" and is_test = false                                 ");
        if (StringUtils.isNotBlank(provinceId)) {
            sb2.append(" and area_id_province = ?                        ");
        }
        if (StringUtils.isNotBlank(cityId)) {
            sb2.append(" and area_id_city = ?                            ");
        }
        if (StringUtils.isNotBlank(districtId)) {
            sb2.append(" and area_id_district = ?                        ");
        }
        if (StringUtils.isNotBlank(phase) && "p0".equals(phase)) {
            sb2.append(" and is_st0 = true                               ");
        }
        if (StringUtils.isNotBlank(phase) && "p1".equals(phase)) {
            sb2.append(" and is_st1 = true                               ");
        }
        if (StringUtils.isNotBlank(phase) && "p2".equals(phase)) {
            sb2.append(" and is_st2 = true                               ");
        }
        if (StringUtils.isNotBlank(phase) && "p3".equals(phase)) {
            sb2.append(" and is_st3 = true                               ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and school_name like ?                          ");
        }
        if ("1".equals(paixu)) {
            sb2.append(" order by pv desc                                ");
        }
        if ("2".equals(paixu)) {
            sb2.append(" order by latest_article desc                    ");
        }
        if ("3".equals(paixu)) {
            sb2.append(" order by article_count desc                     ");
        }
        if (StringUtils.isBlank(paixu)) {
            sb2.append(" order by pv desc                                ");
        }

        sb2.append(" limit ?,?                                           ");
        paramList.add((pageNum - 1) * 12);
        paramList.add(12);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> HomeSchoolKongJianList(Integer bureauLevel, Integer pageSize, String provinceId, String cityId, String districtId) {
        List<Object> paramList = new ArrayList<>();

        StringBuffer sb = new StringBuffer();
        sb.append(" select *                                        ");
        sb.append("    from yxy_sws.sws_group                       ");
        sb.append(" where web_url is not null and web_url != 'null' ");
        sb.append(" and is_test = false                             ");
        // 省教育局
        if (bureauLevel == 2) {
            sb.append(" and area_id_province = ?                    ");
            paramList.add(provinceId);
            sb.append(" order by is_recommend_province desc, pv desc ");
        }
        // 市教育局
        if (bureauLevel == 1) {
            sb.append(" and area_id_city = ?                        ");
            paramList.add(cityId);
            sb.append(" order by is_recommend_city desc, pv desc    ");
        }
        // 县教育局
        if (bureauLevel == 0) {
            sb.append(" and area_id_district = ?                    ");
            paramList.add(districtId);
            sb.append(" order by is_recommend_district desc, pv desc ");
        }
        sb.append(" limit ?                                         ");
        paramList.add(pageSize);

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("list", list);
        return map;
    }

    @Override
    public Integer allSizeHomeSchoolKongJianDongTai(Integer bureauLevel, String provinceId, String cityId, String districtId) {
        List<Object> paramList = new ArrayList<>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select count(school_id)                         ");
        sb1.append("    from yxy_sws.sws_group                       ");
        sb1.append(" where web_url is not null and web_url != 'null' ");
        sb1.append(" and is_test = false                             ");
        // 省教育局
        if (bureauLevel == 2) {
            sb1.append(" and area_id_province = ?                    ");
            paramList.add(provinceId);
        }
        // 市教育局
        if (bureauLevel == 1) {
            sb1.append(" and area_id_city = ?                        ");
            paramList.add(cityId);
        }
        // 县教育局
        if (bureauLevel == 0) {
            sb1.append(" and area_id_district = ?                    ");
            paramList.add(districtId);
        }
        Integer allSize = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);
        return allSize;
    }

    @Override
    public Map<String, Object> HomeSchoolKongJianDongTaiList(Integer bureauLevel, String provinceId, String cityId, String districtId, Integer pageNum) {
        List<Object> paramList = new ArrayList<>();

        StringBuffer sb = new StringBuffer();
        sb.append(" select *                                        ");
        sb.append("    from yxy_sws.sws_group                       ");
        sb.append(" where web_url is not null and web_url != 'null' ");
        sb.append(" and is_test = false                             ");
        // 省教育局
        if (bureauLevel == 2) {
            sb.append(" and area_id_province = ?                    ");
            paramList.add(provinceId);
        }
        // 市教育局
        if (bureauLevel == 1) {
            sb.append(" and area_id_city = ?                        ");
            paramList.add(cityId);
        }
        // 县教育局
        if (bureauLevel == 0) {
            sb.append(" and area_id_district = ?                    ");
            paramList.add(districtId);
        }
        sb.append(" order by latest_article desc, pv desc           ");
        sb.append(" limit ?,?                                       ");
        paramList.add((pageNum - 1) * 5);
        paramList.add(5);

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> EduSchoolTuiJianList(Integer pageNum, Integer bureauLevel, String provinceId, String cityId, String districtId, String schoolType, String keyword, boolean tuijian) {
        List<Object> paramList = new ArrayList<>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append("  select count(school_id)                           ");
        sb1.append("  from yxy_sws.sws_group                            ");
        sb1.append("  where web_url is not null                         ");
        sb1.append("      and web_url != 'null'                         ");
        sb1.append(" and is_test = false                             ");
        if (StringUtils.isNotBlank(provinceId)) {
            sb1.append(" and area_id_province = ?                    ");
            paramList.add(provinceId);
        }
        if (StringUtils.isNotBlank(cityId) && !"all".equals(cityId)) {
            sb1.append(" and area_id_city = ?                        ");
            paramList.add(cityId);
        }
        if (StringUtils.isNotBlank(districtId) && !"all".equals(districtId)) {
            sb1.append(" and area_id_district = ?                    ");
            paramList.add(districtId);
        }
        if (StringUtils.isNotBlank(schoolType) && !"all".equals(schoolType)) {
            if ("p0".equals(schoolType)) {
                sb1.append(" and is_st0 = ?                      ");
            }
            if ("p1".equals(schoolType)) {
                sb1.append(" and is_st1 = ?                      ");
            }
            if ("p2".equals(schoolType)) {
                sb1.append(" and is_st2 = ?                      ");
            }
            if ("p3".equals(schoolType)) {
                sb1.append(" and is_st3 = ?                      ");
            }
            paramList.add(true);
        }
        if (tuijian) {
            paramList.add(true);
            // 省教育局推荐
            if (bureauLevel == 2) {
                sb1.append(" and is_recommend_province = ?        ");
            }
            // 市教育局推荐
            if (bureauLevel == 1) {
                sb1.append(" and is_recommend_city = ?              ");
            }
            // 县教育局推荐
            if (bureauLevel == 0) {
                sb1.append(" and is_recommend_district = ?          ");
            }
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and school_name like ?                     ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();

        sb2.append("  select *                                         ");
        sb2.append("  from yxy_sws.sws_group                            ");
        sb2.append("  where web_url is not null                         ");
        sb2.append("      and web_url != 'null'                         ");
        sb2.append(" and is_test = false                              ");
        if (StringUtils.isNotBlank(provinceId)) {
            sb2.append(" and area_id_province = ?                       ");
        }
        if (StringUtils.isNotBlank(cityId) && !"all".equals(cityId)) {
            sb2.append(" and area_id_city = ?                           ");
        }
        if (StringUtils.isNotBlank(districtId) && !"all".equals(districtId)) {
            sb2.append(" and area_id_district = ?                       ");
        }
        if (StringUtils.isNotBlank(schoolType)) {
            if ("p0".equals(schoolType)) {
                sb2.append(" and is_st0 = ?                             ");
            }
            if ("p1".equals(schoolType)) {
                sb2.append(" and is_st1 = ?                             ");
            }
            if ("p2".equals(schoolType)) {
                sb2.append(" and is_st2 = ?                             ");
            }
            if ("p3".equals(schoolType)) {
                sb2.append(" and is_st3 = ?                             ");
            }
        }
        if (tuijian) {
            // 省教育局推荐
            if (bureauLevel == 2) {
                sb2.append(" and is_recommend_province = ?              ");
            }
            // 市教育局推荐
            if (bureauLevel == 1) {
                sb2.append(" and is_recommend_city = ?                  ");
            }
            // 县教育局推荐
            if (bureauLevel == 0) {
                sb2.append(" and is_recommend_district = ?              ");
            }
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and school_name like ?                         ");
        }
        sb2.append(" order by is_st0 desc,is_st1 desc,is_st2 desc,is_st3 desc, pv desc   ");
        sb2.append(" limit ?,?                                          ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public void updRecommend(String schoolId, boolean tuijian, Integer bureauLevel) {
        StringBuffer sb = new StringBuffer();
        sb.append(" update yxy_sws.sws_group                        ");
        if (bureauLevel == 2) {
            sb.append(" set is_recommend_province = ?               ");
        }
        if (bureauLevel == 1) {
            sb.append(" set is_recommend_city = ?                   ");
        }
        if (bureauLevel == 0) {
            sb.append(" set is_recommend_district = ?               ");
        }
        sb.append(" where school_id = ?                             ");

        jdbcTemplate.update(sb.toString(), new Object[] { tuijian, schoolId });
    }
}
